import pymysql
import random
import time

MYSQL_CONF = {
    "host": "127.0.0.1",
    "user": "root",
    "password": "lxd335388",
    "db": "music"
}

# 连接数据库
mysql_con = pymysql.connect(**MYSQL_CONF)

# 真正执行语句的线程
mysql_cursor = mysql_con.cursor()

category_list = ['流行', '日语']
singer_list = ['a', 'b', 'c']


def get_random_index(lst):
    return lst[random.randint(0, len(lst)-1)]


def clock_it_deco(func):
    def wrapper(*args, **kwargs):
        start_time = time.time()
        result = func(*args, **kwargs)
        end_time = time.time()
        print(f"{func.__name__} execute time: {format(end_time - start_time)}s")
        return result
    return wrapper


# 4.2s
@clock_it_deco
def insert_one():
    for i in range(10**4):
        music_name = f"音乐_{i}"
        play_count = random.randint(10**3, 10**7)
        category = get_random_index(category_list)
        singer = get_random_index(singer_list)
        create_time = time.strftime("%Y-%m-%d")

        SQL = f"""INSERT INTO info(name,category,play_count,singer,create_time)
                  VALUES('{music_name}','{category}',{play_count},'{singer}', '{create_time}')"""
        mysql_cursor.execute(SQL)
        # 显性执行commit，避免cursor执行，但数据库未收到指令的情况
        mysql_con.commit()


#0.22s
@clock_it_deco
def insert_many():
    values = []
    for i in range(10 ** 4):
        music_name = f"音乐_{i}"
        play_count = random.randint(10 ** 3, 10 ** 7)
        category = get_random_index(category_list)
        singer = get_random_index(singer_list)
        create_time = time.strftime("%Y-%m-%d")
        values.append((music_name, category, play_count, singer, create_time))

    SQL = """INSERT INTO info(name, category, play_count, singer, create_time) VALUES (%s, %s, %s, %s, %s)"""
    mysql_cursor.executemany(SQL, values)
    mysql_con.commit()

def get_names():
    SQL = "SELECT * FROM info WHERE singer='a' LIMIT 10"
    mysql_cursor.execute(SQL)
    # 获取返回值
    query_set = mysql_cursor.fetchall()
    print(query_set)

if __name__ == "__main__":
    get_names()